using System;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using WorkTracker.Exceptions;

namespace WorkTracker.BusinessObjects
{
	/// <summary>
	/// Encapsulates data acess for all components.
	/// </summary>
	public class DataAccessLayer
	{

		#region Properties

		/// <summary>
		/// Connection string for the database (gathered from the app.config file).
		/// </summary>
		public static string ConnectionString {
			get { return _connectionString; }
		}
		private static string _connectionString;

		/// <summary>
		/// Open connection.
		/// </summary>
		public static OleDbConnection Connection {
			get {
                if (_connection == null)
                    InitializeConnection();
                return _connection; 
            }
		}
		private static OleDbConnection _connection;

		#endregion


		#region Status change data access methods

		/// <summary>
		/// Loads the last status chage for a user.
		/// </summary>
		/// <param name="user">User for which to get the status change.</param>
		/// <returns>Last status change.</returns>
		public static StatusChange GetLastStatusChange(User user) {
			OleDbCommand command = new OleDbCommand();
			command.Connection = Connection;
			OleDbDataReader reader = null;
			try {
				command.CommandText = "select top 1 UserID, DateChanged, Status from StatusChanges where UserID = ? order by DateChanged desc";
				command.Parameters.Add("UserID", OleDbType.Integer).Value = user.ID;
				reader = command.ExecuteReader();
				if (!reader.HasRows)
					return null;

				StatusChange statusChange = new StatusChange();
				reader.Read();
				statusChange.User = GetUser(reader.GetInt32(0));
				statusChange.Date = reader.GetDateTime(1);
				statusChange.Status = (StatusChange.eStatus)reader.GetInt32(2);
				return statusChange;
			}catch(OleDbException ex) {
				throw new DatabaseException(string.Format(Properties.Resources.Err_UnableToReadDB, ex.Message), ex);				
			} finally {
				if (reader != null)
					reader.Dispose();
				command.Dispose();
			}
		}

		/// <summary>
		/// Gets a list of status changes.
		/// </summary>
		/// <param name="user">User for which to get the status change.</param>
		/// <param name="from">Get status changes from this date.</param>
		/// <param name="to">Get status changes up to this date.</param>
		/// <returns></returns>
		public static List<StatusChange> GetStatusChanges(User user, DateTime from, DateTime to) {
			List<StatusChange> list = new List<StatusChange>();
			OleDbCommand command = new OleDbCommand();
			command.Connection = Connection;
			OleDbDataReader reader = null;
			try {
				command.CommandText = "select UserID, DateChanged, Status from StatusChanges where UserID = ? and DateChanged between ? and ?";
				command.Parameters.Add("UserID", OleDbType.Integer).Value = user.ID;
				command.Parameters.Add("From", OleDbType.Date).Value = from;
				command.Parameters.Add("To", OleDbType.Date).Value = to;

				reader = command.ExecuteReader();
				while (reader.Read()) {
					StatusChange statusChange = new StatusChange();
					statusChange.User = GetUser(reader.GetInt32(0));
					statusChange.Date = reader.GetDateTime(1);
					statusChange.Status = (StatusChange.eStatus)reader.GetInt32(2);
					list.Add(statusChange);
				}
				return list;
			} catch (OleDbException ex) {
				throw new DatabaseException(string.Format(Properties.Resources.Err_UnableToReadDB, ex.Message), ex);
			} finally {
				if (reader != null)
					reader.Dispose();
				command.Dispose();
			}

		}

		/// <summary>
		/// Inserts new status change.
		/// </summary>
		/// <param name="statusChange">Status change to insert.</param>
		public static void InsertStatusChange(StatusChange statusChange) {
			OleDbCommand command = new OleDbCommand();
			command.Connection = Connection;
			try {
				command.CommandText = "insert into StatusChanges(UserID, DateChanged, Status) values(?, ?, ?)";
				command.Parameters.Add("UserID", OleDbType.Integer).Value = statusChange.User.ID;
				command.Parameters.Add("DateChanged", OleDbType.Date).Value = statusChange.Date;
				command.Parameters.Add("Status", OleDbType.Integer).Value = (int)statusChange.Status;
				if (command.ExecuteNonQuery() != 1) {
					throw new DatabaseException(string.Format(Properties.Resources.Err_UnableToWriteDB));
				}
			}catch(OleDbException ex) {
				throw new DatabaseException(string.Format(Properties.Resources.Err_UnableToWriteDB, ex.Message), ex);				
			} finally {
				command.Dispose();
			}
		}

		/// <summary>
		/// Deletes status change.
		/// </summary>
		/// <param name="statusChange">Status change to delete.</param>
		public static void DeleteStatusChange(StatusChange statusChange) {
			OleDbCommand command = new OleDbCommand();
			command.Connection = Connection;
			try {
				command.CommandText = "delete from StatusChanges where UserID = ? and DateChanged = ? and Status = ?";
				command.Parameters.Add("UserID", OleDbType.Integer).Value = statusChange.User.ID;
				command.Parameters.Add("DateChanged", OleDbType.Date).Value = statusChange.Date;
				command.Parameters.Add("Status", OleDbType.Integer).Value = (int)statusChange.Status;
				if (command.ExecuteNonQuery() != 1) {
					throw new DatabaseException(string.Format(Properties.Resources.Err_UnableToWriteDB));
				}
			} catch (OleDbException ex) {
				throw new DatabaseException(string.Format(Properties.Resources.Err_UnableToWriteDB, ex.Message), ex);
			} finally {
				command.Dispose();
			}
		}

		#endregion

		#region User data access methods

		/// <summary>
		/// Gets a user by his name.
		/// </summary>
		/// <param name="name">Name of the user.</param>
		/// <returns>User or null if user was not found.</returns>
		public static User GetUser(string name) {
			OleDbCommand command = new OleDbCommand();
			command.Connection = Connection;
			OleDbDataReader reader = null;
			try {
				command.CommandText = "select ID, UserName, WorkHoursPerDay, MaximumBreakPerDay from Users where UserName = ?";
				command.Parameters.Add("UserName", OleDbType.VarChar).Value = name;
				reader = command.ExecuteReader();
				if (!reader.HasRows)
					return null;

				User user = new User();
				reader.Read();
				user.ID = reader.GetInt32(0);
				user.Name = reader.GetString(1);
				user.WorkHoursPerDay = reader.GetInt32(2);
				user.MaximumBreakPerDay = reader.GetInt32(3);
				return user;
			} catch (OleDbException ex) {
				throw new DatabaseException(string.Format(Properties.Resources.Err_UnableToReadDB, ex.Message), ex);
			} finally {
				if (reader != null)
					reader.Dispose();
				command.Dispose();
			}
		}

		/// <summary>
		/// Gets a user by his database ID.
		/// </summary>
		/// <param name="id">Id of the user.</param>
		/// <returns>User or null if user was not found.</returns>
		public static User GetUser(int id) {
			OleDbCommand command = new OleDbCommand();
			command.Connection = Connection;
			OleDbDataReader reader = null;
			try {
				command.CommandText = "select ID, UserName, WorkHoursPerDay, MaximumBreakPerDay from Users where ID = ?";
				command.Parameters.Add("ID", OleDbType.Integer).Value = id;
				reader = command.ExecuteReader();
				if (!reader.HasRows)
					return null;

				User user = new User();
				reader.Read();
				user.ID = reader.GetInt32(0);
				user.Name = reader.GetString(1);
				user.WorkHoursPerDay = reader.GetInt32(2);
				user.MaximumBreakPerDay = reader.GetInt32(3);
				return user;
			} catch (OleDbException ex) {
				throw new DatabaseException(string.Format(Properties.Resources.Err_UnableToReadDB, ex.Message), ex);
			} finally {
				if (reader != null)
					reader.Dispose();
				command.Dispose();
			}
		}

		/// <summary>
		/// Inserts new user into database.
		/// This method also fills the ID field of the user.
		/// </summary>
		/// <param name="user">User to insert.</param>
		public static void InsertUser(User user) {
			OleDbCommand command = new OleDbCommand();
			command.Connection = Connection;
			OleDbDataReader reader = null;
			try {
				command.CommandText = "insert into Users(UserName, WorkHoursPerDay) values (?, ?)";
				command.Parameters.Add("UserName", OleDbType.VarChar).Value = user.Name;
				command.Parameters.Add("WorkHoursPerDay", OleDbType.Integer).Value = user.WorkHoursPerDay;
				if (command.ExecuteNonQuery() != 1) {
					throw new DatabaseException(string.Format(Properties.Resources.Err_UnableToWriteDB));
				}
				user.ID = GetUser(user.Name).ID;
			} catch (OleDbException ex) {
				throw new DatabaseException(string.Format(Properties.Resources.Err_UnableToReadDB, ex.Message), ex);
			} finally {
				if (reader != null)
					reader.Dispose();
				command.Dispose();
			}
		}

		#endregion

        public static void InitializeConnection() {
            if (_connection != null)
                return;

 			_connectionString = ConfigurationManager.AppSettings["ConnectionString"];
			if (_connectionString == null)
				throw new Exceptions.InvalidConfigurationException(Properties.Resources.Err_InvalidConfiguration);
			try {
				_connection = new OleDbConnection(_connectionString);
				_connection.Open();
			} catch(OleDbException ex) {
				throw new Exceptions.DatabaseException(string.Format(Properties.Resources.Err_UnableToConnectToDB, ex.Message), ex);
			}
       }

	}
}
